<!DOCTYPE html>
<html lang="en">
    <head prefix="og: https://ogp.me/ns#">
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <meta name="color-scheme" content="light dark">
  
  <title>day03-JDBC - Satellite</title>
  
    <link rel="shortcut icon" href="/favicon.png">
  
  
    <link rel='manifest' href='/manifest.json'>
  

  
  
  
  <meta property="og:title" content="day03-JDBC - Satellite" />
  
  <meta property="og:type" content="article" />
  
  <meta property="og:url" content="http://example.com/2023/09/21/day03-JDBC/index.html" />
  
  <meta property="og:image" content="/favicon.png" />
  
  <meta property="og:article:published_time" content="2023-09-21T02:08:36.000Z" />
  
  <meta property="og:article:author" content="John Doe" />
  
  

  
<link rel="stylesheet" href="/css/var.css">

  
<link rel="stylesheet" href="/css/main.css">

  
<link rel="stylesheet" href="/css/typography.css">

  
<link rel="stylesheet" href="/css/code-highlighting.css">

  
<link rel="stylesheet" href="/css/components.css">

  
<link rel="stylesheet" href="/css/nav.css">

  
<link rel="stylesheet" href="/css/paginator.css">

  
<link rel="stylesheet" href="/css/footer.css">

  
<link rel="stylesheet" href="/css/post-list.css">

  
  
<link rel="stylesheet" href="/css/rainbow-banner.css">

  
  
  
<link rel="stylesheet" href="/css/toc.css">

  
  
  
  
  
<link rel="stylesheet" href="/css/post.css">

  
  
  
  
  

  
<meta name="generator" content="Hexo 6.3.0"></head>
    <body
        data-color-scheme="auto"
        data-uppercase-categories="true"
        
        data-rainbow-banner="true"
        data-rainbow-banner-shown="auto"
        data-rainbow-banner-month="6"
        data-rainbow-banner-colors="#e50000,#ff8d00,#ffee00,#008121,#004cff,#760188"
        
        data-config-root="/"
        
        data-toc="true"
        data-toc-max-depth="2"
        
        
    >
        <nav id="theme-nav">
    <div class="inner">
        <a class="title" href="/">Satellite&#39;s Blog</a>
        <div class="nav-arrow"></div>
        <div class="nav-items">
            <a class="nav-item nav-item-home" href="/">Home</a>
            
            
            <a class="nav-item" href="/archives">Archives</a>
            
            
            
            <a class="nav-item" href="/about">About</a>
            
            
            
            <a class="nav-item nav-item-github nav-item-icon" href="https://github.com/Satellite2426" target="_blank" aria-label="GitHub">&nbsp;</a>
            
            
        </div>
    </div>
</nav>
        
<article class="post">
    <div class="meta">
        

        
        <div class="date" id="date">
            <span>September</span>
            <span>21,</span>
            <span>2023</span>
        </div>
        

        <h2 class="title">day03-JDBC</h2>
    </div>

    <div class="divider"></div>

    <div class="content">
        <h1 id="1-JDBC"><a href="#1-JDBC" class="headerlink" title="1. JDBC"></a>1. JDBC</h1><h2 id="1-1-JDBC概念"><a href="#1-1-JDBC概念" class="headerlink" title="1.1 JDBC概念"></a>1.1 JDBC概念</h2><ul>
<li>JDBC就是使用Java语言操作关系型数据库的一套API</li>
<li>全程：Java DataBase Connectivity，Java数据库连接</li>
</ul>
<h2 id="1-2-JDBC本质"><a href="#1-2-JDBC本质" class="headerlink" title="1.2 JDBC本质"></a>1.2 JDBC本质</h2><ul>
<li>官方 (sun公司)定义的一套操作所有关系型数据库的规则，即接口</li>
<li>各个数据库厂商去实现这套接口，提供数据库驱动jar包</li>
<li>我们可以使用这套接口(JDBC)编程，真正执行的代码是驱动jar包中的实现类</li>
</ul>
<h2 id="1-3-JDBC好处"><a href="#1-3-JDBC好处" class="headerlink" title="1.3 JDBC好处"></a>1.3 JDBC好处</h2><ul>
<li>各数据库厂商使用相同的接口，Java代码不需要针对不同数据库分别开发</li>
<li>可随时替换底层数据库，访问数据库的Java代码基本不变</li>
</ul>
<h1 id="2-JDBC快速入门"><a href="#2-JDBC快速入门" class="headerlink" title="2. JDBC快速入门"></a>2. JDBC快速入门</h1><h2 id="2-1-步骤"><a href="#2-1-步骤" class="headerlink" title="2.1 步骤"></a>2.1 步骤</h2><p><strong>0.创建工程，导入驱动jar包</strong></p>
<p><strong>1.注册驱动</strong></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// 不能使用了，说已经改名</span></span><br><span class="line">Class.forName(<span class="string">&quot;com.mysql.jdbc.Driver&quot;</span>);</span><br><span class="line">              </span><br><span class="line"><span class="comment">// 正确修改</span></span><br><span class="line">Class.forName(<span class="string">&quot;com.mysql.cj.jdbc.Driver&quot;</span>);</span><br></pre></td></tr></table></figure>

<blockquote>
<p>MySQL 5之后可以不写，原因是jar包的services目录下的java.sql.Driver记录了对应的驱动的类名称</p>
</blockquote>
<p><strong>2.获取连接</strong></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="type">String</span> <span class="variable">url</span> <span class="operator">=</span> <span class="string">&quot;jdbc:mysql://127.0.0.1:3306/db1?serverTimezone=UTC&amp;useSSL=false&quot;</span>;</span><br><span class="line"><span class="type">String</span> <span class="variable">username</span> <span class="operator">=</span> <span class="string">&quot;root&quot;</span>;</span><br><span class="line"><span class="type">String</span> <span class="variable">password</span> <span class="operator">=</span> <span class="string">&quot;123456&quot;</span>;</span><br><span class="line"><span class="type">Connection</span> <span class="variable">conn</span> <span class="operator">=</span> DriverManager.getConnection(url, username, password);</span><br></pre></td></tr></table></figure>

<blockquote>
<p>注意：serverTimezone时区设置和useSSL控制台warn问题</p>
<p>​				url后添加<code>useServerPrepStmts=true</code>开启预编译功能</p>
</blockquote>
<p><strong>3.定义SQL语句</strong></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="type">String</span> <span class="variable">sql</span> <span class="operator">=</span> <span class="string">&quot;update account set money = 2000 where id = 1&quot;</span>;</span><br></pre></td></tr></table></figure>

<p><strong>4.获取执行SQL对象</strong></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="type">Statement</span> <span class="variable">stmt</span> <span class="operator">=</span> conn.createStatement();</span><br></pre></td></tr></table></figure>

<p><strong>5.执行SQL</strong></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="type">int</span> <span class="variable">count</span> <span class="operator">=</span> stmt.executeUpdate(sql);  <span class="comment">// 受影响行数</span></span><br></pre></td></tr></table></figure>

<blockquote>
<p>后面会有更复杂的操作</p>
</blockquote>
<p><strong>6.处理返回结果</strong></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">System.out.println(count);</span><br></pre></td></tr></table></figure>

<p><strong>7.释放资源</strong></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">stmt.close();</span><br><span class="line">conn.close();</span><br></pre></td></tr></table></figure>

<blockquote>
<p>注意释放顺序</p>
</blockquote>
<h1 id="3-JDBC-API详解"><a href="#3-JDBC-API详解" class="headerlink" title="3. JDBC API详解"></a>3. JDBC API详解</h1><h2 id="3-1-DriverManager"><a href="#3-1-DriverManager" class="headerlink" title="3.1 DriverManager"></a>3.1 DriverManager</h2><p><strong>DriverManager（驱动管理类）作用</strong>：</p>
<ol>
<li>注册驱动</li>
<li>获取数据库连接</li>
</ol>
<p><strong>获取连接</strong>：</p>
<table>
<thead>
<tr>
<th>static Connection</th>
<th>getConnection(String url, String user，String password)</th>
</tr>
</thead>
</table>
<p><strong>参数</strong>：</p>
<ul>
<li>url：连接路径</li>
</ul>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">语法: jdbc:mysql://ip地址(域名):端口号/数据库名称?参数键值对1&amp;参数键值对2...</span><br><span class="line">示例: jdbc:mysql://127.0.0.1:3306/db1?serverTimezone=UTC&amp;useSSL=false</span><br><span class="line">细节:如果连接的是本机mysql服务器，并且mysql服务默认端口是3306，则url可以简写为: dbc:mysql://数据库名称?参数键值对</span><br><span class="line">配置useSSL=false 参数，禁用安全连接方式，解决警告提示</span><br></pre></td></tr></table></figure>

<ul>
<li><p>user：用户名 <code>root </code>  </p>
</li>
<li><p>password：密码 <code>12345 </code></p>
</li>
</ul>
<h2 id="3-2-Connection"><a href="#3-2-Connection" class="headerlink" title="3.2 Connection"></a>3.2 Connection</h2><p><strong>Connection（数据库连接对象）作用</strong>：</p>
<ol>
<li>获取执行 SQL 的对象</li>
<li>管理事务</li>
</ol>
<h3 id="3-2-1-获取执行-SQL-的对象"><a href="#3-2-1-获取执行-SQL-的对象" class="headerlink" title="3.2.1 获取执行 SQL 的对象"></a>3.2.1 获取执行 SQL 的对象</h3><ul>
<li>普通执行SQL对象</li>
</ul>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">Statement <span class="title function_">createStatement</span><span class="params">()</span></span><br></pre></td></tr></table></figure>

<ul>
<li>预编译SQL的执行SQL对象：防止SQL注入</li>
</ul>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">PreparedStatement <span class="title function_">prepareStatement</span><span class="params">(sql)</span></span><br></pre></td></tr></table></figure>

<ul>
<li>执行存储过程的对象</li>
</ul>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">CallableStatement <span class="title function_">prepareCall</span><span class="params">(sql)</span></span><br></pre></td></tr></table></figure>

<h3 id="3-2-2-管理事务"><a href="#3-2-2-管理事务" class="headerlink" title="3.2.2 管理事务"></a>3.2.2 管理事务</h3><p><strong>开启事务</strong>：setAutoCommit(boolean autoCommit)：true为自动提交事务；false为手动提交事务，即为开启事务</p>
<p><strong>提交事务</strong>：commit()</p>
<p><strong>回滚事务</strong>：rollback()</p>
<blockquote>
<p>利用Java的异常处理机制 try&#x2F;catch 开启事务</p>
</blockquote>
<h3 id="3-2-3-Statement"><a href="#3-2-3-Statement" class="headerlink" title="3.2.3 Statement"></a>3.2.3 Statement</h3><p>作用：用于执行SQL语句</p>
<ul>
<li><code>int executeUpdate(sql)</code>：执行DML、DDL语句。返回值：（1）DML语句影响的行数（2）DDL语句执行后，执行成功也可能返回0</li>
<li><code>ResultSet executeQuery(sql)</code>：执行DQL语句。返回值：ResultSet结果集对象</li>
</ul>
<h3 id="3-2-4-ResultSet"><a href="#3-2-4-ResultSet" class="headerlink" title="3.2.4 ResultSet"></a>3.2.4 ResultSet</h3><p><strong>作用</strong>：封装了DQL查询语句的结果</p>
<ul>
<li><code>ResultSet stmt.executeQuery(sql)</code>：执行DQL语句，返回ResultSet对象</li>
</ul>
<p><strong>获取查询结果</strong></p>
<ul>
<li><code>boolean next()</code>：（1）将光标从当前位置向前移动一行（2）判断当前行是否为有效行。返回值：true：有效行，当前行有数据；fasle：无效行，当前行没有数据</li>
<li><code>xxx getXxx(参数)</code>：获取数据。xxx代表数据类型，如：int getInt(参数)；String getString(参数)。参数：int列的编号，从1开始；String列的名称</li>
</ul>
<p><strong>使用步骤</strong></p>
<ol>
<li>游标向下移动一行，并判断该行否有数据: next()</li>
<li>获取数据：getXxx(参数)</li>
</ol>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// 循环判断游标是否是最后一行末尾</span></span><br><span class="line"><span class="keyword">while</span> (rs.next())&#123;</span><br><span class="line">	<span class="comment">// 获取数据</span></span><br><span class="line">	rs.getXxx(参数);</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<h3 id="3-2-5-PreparedStatement"><a href="#3-2-5-PreparedStatement" class="headerlink" title="3.2.5 PreparedStatement"></a>3.2.5 PreparedStatement</h3><p>作用：继承自Statement。预编译SQL语句并执行，预防SQL注入问题</p>
<blockquote>
<p>SQL注入是通过操作输入来修改事先定义好的SQL语句，用以达到执行代码对服务器进行&#x3D;&#x3D;攻击&#x3D;&#x3D;的方法</p>
</blockquote>
<ol>
<li>获取PreparedStatement对象</li>
</ol>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// SQL语句中的参数值，使用? 占位符替代</span></span><br><span class="line"><span class="type">String</span> <span class="variable">sql</span> <span class="operator">=</span> <span class="string">&quot;select * from user where username = ? and password = ?&quot;</span>;</span><br><span class="line"><span class="comment">// 通过Connection对象获取，并传入对应的sql语句</span></span><br><span class="line"><span class="type">PreparedStatement</span> <span class="variable">pstmt</span> <span class="operator">=</span> conn.prepareStatement(sql);</span><br></pre></td></tr></table></figure>

<ol start="2">
<li>设置参数值</li>
</ol>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">PreparedStatement对象: setXxx(参数<span class="number">1</span>，参数<span class="number">2</span>): 给?赋值</span><br><span class="line">Xxx:数据类型;如 setlnt(参数<span class="number">1</span>，参数<span class="number">2</span>)</span><br><span class="line">参数:</span><br><span class="line">	参数<span class="number">1</span>：?的位置编号，从<span class="number">1</span>开始</span><br><span class="line">	参数<span class="number">2</span>：?的值</span><br></pre></td></tr></table></figure>

<ol start="3">
<li>执行SQL</li>
</ol>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">executeUpdate();/executeQuery();       不需要再传递sql</span><br></pre></td></tr></table></figure>

<h1 id="4-数据库连接池"><a href="#4-数据库连接池" class="headerlink" title="4. 数据库连接池"></a>4. 数据库连接池</h1><h2 id="4-1-简介"><a href="#4-1-简介" class="headerlink" title="4.1 简介"></a>4.1 简介</h2><p><strong>数据库连接池</strong>是个容器，负责分配、管理数据库连接(Connection)</p>
<ul>
<li>它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个</li>
<li>释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏</li>
<li>好处：</li>
</ul>
<p>​			- 资源重用</p>
<p>​			- 提升系统响应速度</p>
<p>​			- 避免数据库连接遗漏</p>
<h2 id="4-2-实现"><a href="#4-2-实现" class="headerlink" title="4.2 实现"></a>4.2 实现</h2><h3 id="4-2-1-标准接口"><a href="#4-2-1-标准接口" class="headerlink" title="4.2.1 标准接口"></a>4.2.1 标准接口</h3><p><strong>DataSource</strong></p>
<ul>
<li>官方(SUN)提供的数据库连接池标准接口，由第三方组织实现此接口。</li>
<li>功能：获取连接</li>
</ul>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">Connection <span class="title function_">getConnection</span><span class="params">()</span></span><br></pre></td></tr></table></figure>

<h3 id="4-2-2-常见的数据库连接池"><a href="#4-2-2-常见的数据库连接池" class="headerlink" title="4.2.2 常见的数据库连接池"></a>4.2.2 常见的数据库连接池</h3><ul>
<li><p>DBCP</p>
</li>
<li><p>C3P0</p>
</li>
<li><p>Druid：Druid连接池是阿里巴巴开源的数据库连接池项目；功能强大，性能优秀，是Java语言最好的数据库连接池之一</p>
</li>
</ul>
<h3 id="4-2-3-Druid使用步骤"><a href="#4-2-3-Druid使用步骤" class="headerlink" title="4.2.3 Druid使用步骤"></a>4.2.3 Druid使用步骤</h3><ol>
<li>导入jar包</li>
<li>定义配置文件</li>
<li>加载配置文件</li>
<li>获取数据库连接池对象</li>
<li>获取连接</li>
</ol>
<blockquote>
<p><strong>IDEA</strong></p>
<p>​		alt + 鼠标左键：整列编辑</p>
</blockquote>

    </div>

    
    <div class="about">
        <h1>About this Post</h1>
        <div class="details">
            <p>This post is written by John Doe, licensed under <a target="_blank" rel="noopener" href="https://creativecommons.org/licenses/by-nc/4.0">CC BY-NC 4.0</a>.</p>
        </div>
        
        <p class="tags text-capitalize">
            
            <i class="icon"></i>
            <a href="/tags/Java-Web/" class="tag">#Java Web</a>
        </p>
        
    </div>
    

    <div class="container post-prev-next">
        
        <a href="/2023/09/21/day04-Maven/" class="next">
            <div>
                <div class="text">
                    <p class="label">Next</p>
                    <h3 class="title">day04-Maven</h3>
                </div>
            </div>
        </a>
        
        
        <a href="/2023/09/21/day02-MySQL/" class="prev">
            <div>
                <div class="text">
                    <p class="label">Previous</p>
                    <h3 class="title">day02-MySQL</>
                </div>
            </div>
        </a>
        
    </div>

    
        
        
    
</article>

        <footer>
    <div class="inner">
        <div class="links">
            
            <div class="group">
                <h2 class="title">Blog</h2>
                
                <a href="/" class="item">Blog</a>
                
                <a href="/archives" class="item">Archives</a>
                
                <a href="/tags" class="item">Tags</a>
                
                <a href="/categories" class="item">Categories</a>
                
                <a href="/about" class="item">About</a>
                
            </div>
            
            <div class="group">
                <h2 class="title">Projects</h2>
                
                <a target="_blank" rel="noopener" href="https://github.com/MrWillCom/rsa-cli" class="item">RSA CLI</a>
                
                <a target="_blank" rel="noopener" href="https://github.com/MrWillCom/hexo-theme-cupertino" class="item">Hexo Theme Cupertino</a>
                
                <a target="_blank" rel="noopener" href="https://github.com/MrWillCom/a-calendar" class="item">A Calendar</a>
                
                <a target="_blank" rel="noopener" href="https://github.com/MrWillCom/auto-mirroring-bucket" class="item">Auto Mirroring Bucket</a>
                
            </div>
            
            <div class="group">
                <h2 class="title">Me</h2>
                
                <a target="_blank" rel="noopener" href="https://github.com/Satellite2426" class="item">GitHub</a>
                
                <a href="mailto:chaoshihan2426@gmail.com" class="item">Email</a>
                
            </div>
            
        </div>
        <span>&copy; 2023 John Doe<br>Powered by <a href="http://hexo.io/" target="_blank">Hexo</a> </span>
        
        
            <br>
            <div class="color-scheme-toggle" role="radiogroup" id="theme-color-scheme-toggle">
                <label>
                    <input type="radio" value="light">
                    <span>Light</span>
                </label>
                <label>
                    <input type="radio" value="dark">
                    <span>Dark</span>
                </label>
                <label>
                    <input type="radio" value="auto">
                    <span>Auto</span>
                </label>
            </div>
        
    </div>
</footer>


        
<script src="/js/main.js"></script>

        
        
        

        
        <script src="https://unpkg.com/scrollreveal"></script>
        <script>
            window.addEventListener('load', () => {
                ScrollReveal({ delay: 250, reset: true, easing: 'cubic-bezier(0, 0, 0, 1)' })
                ScrollReveal().reveal('.post-list-item .cover-img img')
                ScrollReveal().reveal('.post-list-item, .card, .content p img, .content .block-large img', { distance: '60px', origin: 'bottom', duration: 800 })
            })
        </script>
        
    </body>
</html>